
-- 连接的使用 JOIN
/*
在两个或多个表中查询数据。
INNER JOIN（JOIN, 内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 获取右表所有记录，即使左表没有对应匹配的记录。
*/
MariaDB [haydnliao]> CREATE TABLE IF NOT EXISTS tcount_tb1(
	-> author VARCHAR(255) NOT NULL DEFAULT '',
	-> count INT(11) NOT NULL DEFAULT 0
	-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

MariaDB [haydnliao]> INSERT INTO tcount_tb1
	-> VALUES ('Baidu', 10), ('Google', 20), ('Taobao', 22);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> SELECT * FROM tcount_tb1;
+--------+-------+
| author | count |
+--------+-------+
| Baidu  |    10 |
| Google |    20 |
| Taobao |    22 |
+--------+-------+
3 rows in set (0.00 sec)

MariaDB [haydnliao]> SELECT * FROM websites;
+----+---------------+-------------------+---------+
| id | name          | url               | country |
+----+---------------+-------------------+---------+
|  1 | Google        | www.google.com    | USA     |
|  2 | TaoBao        | www.taobao.com    | CN      |
|  3 | Facebook      | www.facebook.com  | USA     |
|  4 | StackOverflow | stackoverflow.com | IND     |
+----+---------------+-------------------+---------+
4 rows in set (0.00 sec)

-- MariaDB [haydnliao]> SELECT a.name, a.url, b.count
--     -> FROM websites As a INNER JOIN tcount_tb1 AS b ON a.name = b.author;
MariaDB [haydnliao]> SELECT a.name, a.url, b.count
	-> FROM websites a JOIN tcount_tb1 b ON a.name = b.author;
+--------+----------------+-------+
| name   | url            | count |
+--------+----------------+-------+
| Google | www.google.com |    20 |
| TaoBao | www.taobao.com |    22 |
+--------+----------------+-------+
2 rows in set (0.00 sec)

MariaDB [haydnliao]> SELECT a.name, a.url, b.count
	-> FROM websites a LEFT JOIN tcount_tb1 b ON a.name = b.author;
+---------------+-------------------+-------+
| name          | url               | count |
+---------------+-------------------+-------+
| Google        | www.google.com    |    20 |
| TaoBao        | www.taobao.com    |    22 |
| Facebook      | www.facebook.com  |  NULL |
| StackOverflow | stackoverflow.com |  NULL |
+---------------+-------------------+-------+
4 rows in set (0.01 sec)

MariaDB [haydnliao]> SELECT a.name, a.url, b.count
	-> FROM websites a RIGHT JOIN tcount_tb1 b ON a.name = b.author;
+--------+----------------+-------+
| name   | url            | count |
+--------+----------------+-------+
| Google | www.google.com |    20 |
| TaoBao | www.taobao.com |    22 |
| NULL   | NULL           |    10 |
+--------+----------------+-------+
3 rows in set (0.00 sec)

-- NULL 值处理
/*
MySQL提供了三大运算符:
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符，当比较的的两个值为 NULL 时返回 true。
*/
/*
不能使用 = NULL 或 != NULL 在列中查找 NULL 值。
NULL 值与任何其它值的比较（即使是 NULL）永远返回 false。
*/
MariaDB [haydnliao]> SELECT a.name,b.count
	-> FROM websites a LEFT JOIN tcount_tb1 b ON a.name=b.author
	-> WHERE b.count IS NULL;
+---------------+-------+
| name          | count |
+---------------+-------+
| Facebook      |  NULL |
| StackOverflow |  NULL |
+---------------+-------+
2 rows in set (0.00 sec)

-- 正则表达式操作符 REGEXP
/*
^		匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$		匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.		匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
*		匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+		匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}		n是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
*/
-- 查找name字段中以'O,P,Q,R,S,T'开头或以'ok'字符串结尾的所有数据
MariaDB [haydnliao]> SELECT * FROM websites
	-> WHERE name REGEXP '^[OPQRST]|ok$';
+----+---------------+-------------------+---------+
| id | name          | url               | country |
+----+---------------+-------------------+---------+
|  2 | TaoBao        | www.taobao.com    | CN      |
|  3 | Facebook      | www.facebook.com  | USA     |
|  4 | StackOverflow | stackoverflow.com | IND     |
+----+---------------+-------------------+---------+
3 rows in set (0.00 sec)

-- 事务
/*
事务主要用于处理操作量大，复杂度高的数据。
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句。
*/
/*
事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
1、原子性：一组事务，要么成功；要么撤回。
2、稳定性 ：有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。
可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项决定什么时候把事务保存到日志里。
*/
/*
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。
因此要显式地开启一个事务须使用命令 BEGIN 或 START TRANSACTION，
或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
*/
/*
事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
	BEGIN 开始一个事务
	[ROLLBACK 事务回滚]
	COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
	SET AUTOCOMMIT=0 禁止自动提交
	SET AUTOCOMMIT=1 开启自动提交
*/
/*
事务控制语句：
BEGIN 或 START TRANSACTION 显式地开启一个事务。
COMMIT 或 COMMIT WORK 提交事务，并使已对数据库进行的所有修改成为永久性的。
ROLLBACK 或 ROLLBACK WORK 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
SAVEPOINT identifier 允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常。
ROLLBACK TO identifier 把事务回滚到标记点。
SET TRANSACTION 用来设置事务的隔离级别。
InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
*/
MariaDB [haydnliao]> CREATE TABLE IF NOT EXISTS transaction_tb(
    -> id INT UNSIGNED AUTO_INCREMENT,
    -> num INT(5) NOT NULL DEFAULT 0,
    -> PRIMARY KEY (id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

ariaDB [haydnliao]> SELECT * FROM transaction_tb;
+----+-----+
| id | num |
+----+-----+
|  1 |   5 |
|  2 |   8 |
+----+-----+
2 rows in set (0.00 sec)

MariaDB [haydnliao]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [haydnliao]> INSERT INTO transaction_tb (num) VALUES (3);
Query OK, 1 row affected (0.00 sec)

MariaDB [haydnliao]> SELECT * FROM transaction_tb;
+----+-----+
| id | num |
+----+-----+
|  1 |   5 |
|  2 |   8 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

MariaDB [haydnliao]> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

MariaDB [haydnliao]> SELECT * FROM transaction_tb;
+----+-----+
| id | num |
+----+-----+
|  1 |   5 |
|  2 |   8 |
+----+-----+
2 rows in set (0.00 sec)

-- ALTER 命令
-- 修改数据表名或者修改数据表字段.

-- 添加表字段 ALTER-ADD
-- 不设置默认值，会自动设置该字段默认为 NULL。
MariaDB [haydnliao]> ALTER TABLE transaction_tb
    -> ADD add_one VARCHAR(8);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
-- 关键字 FIRST  第一列
MariaDB [haydnliao]> ALTER TABLE transaction_tb
    -> ADD add_two VARCHAR(8) FIRST;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0
-- 关键字 AFTER 字段名  位于某个字段后
MariaDB [haydnliao]> ALTER TABLE transaction_tb
    -> ADD add_three VARCHAR(8) AFTER id;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> SELECT * FROM transaction_tb;
+---------+----+-----------+-----+---------+
| add_two | id | add_three | num | add_one |
+---------+----+-----------+-----+---------+
| NULL    |  1 | NULL      |   5 | NULL    |
| NULL    |  2 | NULL      |   8 | NULL    |
+---------+----+-----------+-----+---------+
2 rows in set (0.00 sec)

-- 删除表字段 ALTER-DROP
MariaDB [haydnliao]> ALTER TABLE transaction_tb DROP add_two;
Query OK, 2 rows affected (0.01 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> ALTER TABLE transaction_tb DROP add_three;
Query OK, 2 rows affected (0.06 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> SELECT * FROM transaction_tb;
+----+-----+---------+
| id | num | add_one |
+----+-----+---------+
|  1 |   5 | NULL    |
|  2 |   8 | NULL    |
+----+-----+---------+
2 rows in set (0.00 sec)

-- 修改表字段
-- 修改字段类型 ALTER-MODIFY
MariaDB [haydnliao]> SHOW COLUMNS FROM transaction_tb;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| num     | int(5)           | NO   |     | 0       |                |
| add_one | varchar(8)       | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [haydnliao]> ALTER TABLE transaction_tb 
    -> MODIFY add_one VARCHAR(3);
Query OK, 2 rows affected (0.01 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> SHOW COLUMNS FROM transaction_tb;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| num     | int(5)           | NO   |     | 0       |                |
| add_one | varchar(3)       | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

-- 修改字段类型和名称 ALTER-CHANGE
MariaDB [haydnliao]> ALTER TABLE transaction_tb
    -> CHANGE add_one add_change varchar(10);
Query OK, 2 rows affected (0.01 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> SHOW COLUMNS FROM transaction_tb;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| num        | int(5)           | NO   |     | 0       |                |
| add_change | varchar(10)      | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

-- 修改字段默认值 ALTER-ALTER-SET
MariaDB [haydnliao]> ALTER TABLE transaction_tb
    -> ALTER add_change SET DEFAULT 'def';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> SHOW COLUMNS FROM transaction_tb;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| num        | int(5)           | NO   |     | 0       |                |
| add_change | varchar(10)      | YES  |     | def     |                |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

-- 删除字段默认值 ALTER-ALTER-DROP
MariaDB [haydnliao]> ALTER TABLE transaction_tb
    -> ALTER add_change DROP DEFAULT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> SHOW COLUMNS FROM transaction_tb;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| num        | int(5)           | NO   |     | 0       |                |
| add_change | varchar(10)      | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

-- 修改数据表类型 ALTER TABLE table_name table_type
MariaDB [haydnliao]> SHOW TABLE STATUS LIKE 'transaction_tb' \G;
*************************** 1. row ***************************
           Name: transaction_tb
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 8388608
 Auto_increment: 6
    Create_time: 2017-07-03 23:19:53
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [haydnliao]> ALTER TABLE transaction_tb ENGINE=MYISAM;
Query OK, 2 rows affected (0.01 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [haydnliao]> SHOW TABLE STATUS LIKE 'transaction_tb' \G;
*************************** 1. row ***************************
           Name: transaction_tb
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 2
 Avg_row_length: 9
    Data_length: 18
Max_data_length: 2533274790395903
   Index_length: 2048
      Data_free: 0
 Auto_increment: 6
    Create_time: 2017-07-03 23:23:18
    Update_time: 2017-07-03 23:23:18
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

-- 修改数据表的名称 ALTER-RENAME TO
MariaDB [haydnliao]> SHOW TABLES;
+---------------------+
| Tables_in_haydnliao |
+---------------------+
| apps                |
| haydn_tb1           |
| login               |
| tcount_tb1          |
| transaction_tb      |
| websites            |
+---------------------+
6 rows in set (0.00 sec)

MariaDB [haydnliao]> ALTER TABLE transaction_tb
    -> RENAME TO trans_tb;
Query OK, 0 rows affected (0.04 sec)

MariaDB [haydnliao]> SHOW TABLES;
+---------------------+
| Tables_in_haydnliao |
+---------------------+
| apps                |
| haydn_tb1           |
| login               |
| tcount_tb1          |
| trans_tb            |
| websites            |
+---------------------+
6 rows in set (0.00 sec)

-- 索引
/*
索引可以大大提高MySQL的检索速度。
索引分单列索引和组合索引。
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引，即一个索引包含多个列。
*/
/*
创建索引时，要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
索引会降低更新表的速度，如对表进行INSERT、UPDATE 和 DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
*/
-- 普通索引(三种方法)
-- 创建索引 CREATE INDEX-ON
CREATE INDEX indexName ON mytable(col_name(length)); 
/*
如果是 CHAR 和 VARCHAR 类型，length可以小于字段实际长度；
如果是 BLOB 和 TEXT 类型，必须指定 length 。
*/
MariaDB [haydnliao]> CREATE INDEX web_index ON websites(name(10));
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 修改表结构（添加索引）ALTER-ADD INDEX
ALTER TABLE mytable ADD INDEX [indexName] (col_name(length)) 
MariaDB [haydnliao]> ALTER TABLE websites ADD INDEX web_index2 (name(10));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 创建表的时候直接指定 INDEX
CREATE TABLE mytable(  
...
INDEX [indexName] (col_name(length))
...
);

-- 删除索引 DROP INDEX-ON
DROP INDEX [indexName] ON mytable;
MariaDB [haydnliao]> DROP INDEX web_index2 ON websites;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 唯一索引(三种方法)
/*
索引列的值必须唯一，但允许有空值。
如果是组合索引，则列值的组合必须唯一。
*/
-- 创建索引 CREATE UNIQUE INDEX-ON
CREATE UNIQUE INDEX indexName ON mytable(col_name(length)); 
MariaDB [haydnliao]> CREATE UNIQUE INDEX web_index ON websites (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 修改表结构（添加索引）
ALTER TABLE mytable ADD UNIQUE [indexName] (col_name(length))
MariaDB [haydnliao]> ALTER TABLE websites ADD UNIQUE web_index2 (id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 创建表的时候直接指定
CREATE TABLE mytable(
...
UNIQUE [indexName] (col_name(length))
...
);

-- 使用 ALTER 命令添加索引(四种)
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) -- 主键
-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD INDEX index_name (column_list) -- 普通索引
-- 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) -- 唯一索引
-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) -- 全文索引
-- 该语句指定了索引为 FULLTEXT ，用于全文索引。

-- 使用 ALTER 命令删除索引 ALTER TABLE-DROP INDEX
ALTER TABLE tbl_name DROP PRIMARY KEY; -- 主键
ALTER TABLE tbl_name DROP INDEX index_name; -- 索引
MariaDB [haydnliao]> ALTER TABLE tcount_tb1 DROP PRIMARY KEY;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
MariaDB [haydnliao]> ALTER TABLE websites DROP INDEX web_index;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 显示索引信息
SHOW INDEX FROM tbl_name \G ;
MariaDB [haydnliao]> SHOW INDEX FROM websites \G;
*************************** 1. row ***************************
        Table: websites
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: websites
   Non_unique: 0
     Key_name: web_index
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

ERROR: No query specified
